﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using QQTools.Data.Enums.AddGroup;
using QQTools.Data.Models;
using QQTools.Logging;

namespace QQTools.Data.Repositories.AddGroup
{
    public class AddGroupQQRepository
    {
        private readonly string _connectionString;

        public AddGroupQQRepository(string connectionString)
        {
            _connectionString = connectionString;
        }

        public async Task<List<AddGroupQQModel>> GetAllAsync()
        {
            var addGroupQQs = new List<AddGroupQQModel>();

            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand("SELECT * FROM add_group_qq ORDER BY id ASC", connection))
                {
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {

                            int id = reader.IsDBNull(reader.GetOrdinal("id")) ? 0 : reader.GetInt32(reader.GetOrdinal("id"));
                            string qq = reader.IsDBNull(reader.GetOrdinal("qq")) ? null : reader.GetString(reader.GetOrdinal("qq"));
                            int currentBatch = reader.IsDBNull(reader.GetOrdinal("current_batch")) ? 1 : reader.GetInt32(reader.GetOrdinal("current_batch"));
                            int currentBatchAddCount = reader.IsDBNull(reader.GetOrdinal("current_batch_add_count")) ? 0 : reader.GetInt32(reader.GetOrdinal("current_batch_add_count"));
                            int totalAdd = reader.IsDBNull(reader.GetOrdinal("total_add")) ? 0 : reader.GetInt32(reader.GetOrdinal("total_add"));
                            DateTime createTime = reader.IsDBNull(reader.GetOrdinal("create_time")) ? DateTime.MinValue : reader.GetDateTime(reader.GetOrdinal("create_time"));
                            DateTime updateTime = reader.IsDBNull(reader.GetOrdinal("update_time")) ? DateTime.MinValue : reader.GetDateTime(reader.GetOrdinal("update_time"));


                            addGroupQQs.Add(new AddGroupQQModel
                            {
                                Id = id,
                                QQ = qq,
                                CurrentBatch = (AddGroupQQStatus)currentBatch,
                                CurrentBatchAddCount = currentBatchAddCount,
                                TotalAdd = totalAdd,
                                CreateTime = createTime,
                                UpdateTime = updateTime
                            });
                        }
                    }
                }
            }

            return addGroupQQs;
        }

        public async Task<AddGroupQQModel> GetByIdAsync(int id)
        {
            AddGroupQQModel addGroupQQ = null;

            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand("SELECT * FROM add_group_qq WHERE id = @Id ORDER BY id ASC", connection))
                {
                    command.Parameters.AddWithValue("@Id", id);
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        if (await reader.ReadAsync())
                        {
                            addGroupQQ = new AddGroupQQModel
                            {
                                Id = reader.GetInt32(reader.GetOrdinal("id")),
                                QQ = reader.GetString(reader.GetOrdinal("qq")),
                                CurrentBatch = (AddGroupQQStatus)reader.GetInt32(reader.GetOrdinal("current_batch")),
                                CurrentBatchAddCount = reader.GetInt32(reader.GetOrdinal("current_batch_add_count")),
                                TotalAdd = reader.GetInt32(reader.GetOrdinal("total_add")),
                                CreateTime = reader.GetDateTime(reader.GetOrdinal("create_time")),
                                UpdateTime = reader.GetDateTime(reader.GetOrdinal("update_time"))
                            };
                        }
                    }
                }
            }

            return addGroupQQ;
        }

        public async Task<AddGroupQQModel> GetByQQAsync(string qq)
        {
            AddGroupQQModel addGroupQQ = null;

            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand("SELECT * FROM add_group_qq WHERE qq = @QQ ORDER BY id ASC", connection))
                {
                    command.Parameters.AddWithValue("@QQ", qq);
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        if (await reader.ReadAsync())
                        {
                            addGroupQQ = new AddGroupQQModel
                            {
                                Id = reader.GetInt32(reader.GetOrdinal("id")),
                                QQ = reader.GetString(reader.GetOrdinal("qq")),
                                CurrentBatch = (AddGroupQQStatus)reader.GetInt32(reader.GetOrdinal("current_batch")),
                                CurrentBatchAddCount = reader.GetInt32(reader.GetOrdinal("current_batch_add_count")),
                                TotalAdd = reader.GetInt32(reader.GetOrdinal("total_add")),
                                CreateTime = reader.GetDateTime(reader.GetOrdinal("create_time")),
                                UpdateTime = reader.GetDateTime(reader.GetOrdinal("update_time"))
                            };
                        }
                    }
                }
            }
            return addGroupQQ;
        }

        public async Task AddAsync(AddGroupQQModel addGroupQQ)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
                    INSERT INTO add_group_qq (qq, current_batch, current_batch_add_count, total_add)
                    VALUES (@QQ, @CurrentBatch, @CurrentBatchAddCount, @TotalAdd);", connection))
                {
                    command.Parameters.AddWithValue("@QQ", addGroupQQ.QQ);
                    command.Parameters.AddWithValue("@CurrentBatch", (int)addGroupQQ.CurrentBatch);
                    command.Parameters.AddWithValue("@CurrentBatchAddCount", addGroupQQ.CurrentBatchAddCount);
                    command.Parameters.AddWithValue("@TotalAdd", addGroupQQ.TotalAdd);

                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        public async Task UpdateAsync(AddGroupQQModel addGroupQQ)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
                    UPDATE add_group_qq
                    SET qq = @QQ, current_batch = @CurrentBatch, current_batch_add_count = @CurrentBatchAddCount, total_add = @TotalAdd, update_time = CURRENT_TIMESTAMP
                    WHERE id = @Id;", connection))
                {
                    command.Parameters.AddWithValue("@Id", addGroupQQ.Id);
                    command.Parameters.AddWithValue("@QQ", addGroupQQ.QQ);
                    command.Parameters.AddWithValue("@CurrentBatch", (int)addGroupQQ.CurrentBatch);
                    command.Parameters.AddWithValue("@CurrentBatchAddCount", addGroupQQ.CurrentBatchAddCount);
                    command.Parameters.AddWithValue("@TotalAdd", addGroupQQ.TotalAdd);

                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        public async Task DeleteAsync(int id)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand("DELETE FROM add_group_qq WHERE id = @Id;", connection))
                {
                    command.Parameters.AddWithValue("@Id", id);
                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        /// <summary>
        /// 清空表
        /// </summary>
        /// <returns></returns>
        public async Task ClearTableAsync()
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand("DELETE FROM add_group_qq;", connection))
                {
                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        /// <summary>
        /// 添加或更新
        /// </summary>
        /// <param name="addGroupQQ"></param>
        /// <returns></returns>
        public async Task AddOrUpdateAsync(AddGroupQQModel addGroupQQ)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        // 检查 qq 是否已经存在
                        using (var checkCommand = new SQLiteCommand(@"
                            SELECT id FROM add_group_qq
                            WHERE qq = @QQ
                            ORDER BY id ASC", connection, transaction))
                        {
                            checkCommand.Parameters.AddWithValue("@QQ", addGroupQQ.QQ);
                            var existingId = await checkCommand.ExecuteScalarAsync();

                            if (existingId != null)
                            {
                                // 如果存在，则更新数据
                                int id = Convert.ToInt32(existingId);
                                addGroupQQ.Id = id;
                                await UpdateAsync(addGroupQQ, transaction);
                            }
                            else
                            {
                                // 如果不存在，则插入新数据
                                await AddAsync(addGroupQQ, transaction);
                            }
                        }
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        LogManager.Error("[AddGroupQQRepository] AddOrUpdateAsync", ex);
                    }
                }
            }
        }

        private async Task AddAsync(AddGroupQQModel addGroupQQ, SQLiteTransaction transaction)
        {
            using (var command = new SQLiteCommand(@"
                INSERT INTO add_group_qq (qq, current_batch, current_batch_add_count, total_add)
                VALUES (@QQ, @CurrentBatch, @CurrentBatchAddCount, @TotalAdd);", transaction.Connection, transaction))
            {
                command.Parameters.AddWithValue("@QQ", addGroupQQ.QQ);
                command.Parameters.AddWithValue("@CurrentBatch", (int)addGroupQQ.CurrentBatch);
                command.Parameters.AddWithValue("@CurrentBatchAddCount", addGroupQQ.CurrentBatchAddCount);
                command.Parameters.AddWithValue("@TotalAdd", addGroupQQ.TotalAdd);

                await command.ExecuteNonQueryAsync();
            }
        }

        private async Task UpdateAsync(AddGroupQQModel addGroupQQ, SQLiteTransaction transaction)
        {
            using (var command = new SQLiteCommand(@"
                UPDATE add_group_qq
                SET qq = @QQ, current_batch = @CurrentBatch, current_batch_add_count = @CurrentBatchAddCount, total_add = @TotalAdd, update_time = CURRENT_TIMESTAMP
                WHERE id = @Id;", transaction.Connection, transaction))
            {
                command.Parameters.AddWithValue("@Id", addGroupQQ.Id);
                command.Parameters.AddWithValue("@QQ", addGroupQQ.QQ);
                command.Parameters.AddWithValue("@CurrentBatch", (int)addGroupQQ.CurrentBatch);
                command.Parameters.AddWithValue("@CurrentBatchAddCount", addGroupQQ.CurrentBatchAddCount);
                command.Parameters.AddWithValue("@TotalAdd", addGroupQQ.TotalAdd);

                await command.ExecuteNonQueryAsync();
            }
        }

        /// <summary>
        /// 当前批次添加数量自增1
        /// </summary>
        /// <param name="qq"></param>
        /// <returns></returns>
        public async Task IncrementCurrentBatchAddCountAsync(string qq)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
                    UPDATE add_group_qq
                    SET current_batch_add_count = current_batch_add_count + 1, update_time = CURRENT_TIMESTAMP
                    WHERE qq = @QQ;", connection))
                {
                    command.Parameters.AddWithValue("@QQ", qq);

                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        /// <summary>
        /// 重置当前批次添加数量为0
        /// </summary>
        /// <param name="qq"></param>
        /// <returns></returns>
        public async Task ResetCurrentBatchAddCountAsync(string qq)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
                    UPDATE add_group_qq
                    SET current_batch_add_count = 0, update_time = CURRENT_TIMESTAMP
                    WHERE qq = @QQ;", connection))
                {
                    command.Parameters.AddWithValue("@QQ", qq);

                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        /// <summary>
        /// 更新总添加数量
        /// </summary>
        /// <param name="qq"></param>
        /// <param name="totalAdd"></param>
        /// <returns></returns>
        public async Task UpdateTotalAddAsync(string qq, int totalAdd)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
                    UPDATE add_group_qq
                    SET total_add = @TotalAdd, update_time = CURRENT_TIMESTAMP
                    WHERE qq = @QQ;", connection))
                {
                    command.Parameters.AddWithValue("@QQ", qq);
                    command.Parameters.AddWithValue("@TotalAdd", totalAdd);

                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        /// <summary>
        /// 更新当前批次
        /// </summary>
        /// <param name="qq"></param>
        /// <param name="currentBatch"></param>
        /// <returns></returns>
        public async Task UpdateCurrentBatchAsync(string qq, AddGroupQQStatus currentBatch)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
                    UPDATE add_group_qq
                    SET current_batch = @CurrentBatch, update_time = CURRENT_TIMESTAMP
                    WHERE qq = @QQ;", connection))
                {
                    command.Parameters.AddWithValue("@QQ", qq);
                    command.Parameters.AddWithValue("@CurrentBatch", (int)currentBatch);

                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        /// <summary>
        /// 根据QQ获取
        /// </summary>
        /// <param name="qq"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async Task<AddGroupQQModel> GetByQQAsync(string qq, CancellationToken cancellationToken)
        {
            AddGroupQQModel addGroupQQ = null;

            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
                    SELECT * FROM add_group_qq
                    WHERE qq = @QQ
                    ORDER BY id ASC;", connection))
                {
                    command.Parameters.AddWithValue("@QQ", qq);
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        if (await reader.ReadAsync(cancellationToken))
                        {
                            addGroupQQ = new AddGroupQQModel
                            {
                                Id = reader.GetInt32(reader.GetOrdinal("id")),
                                QQ = reader.GetString(reader.GetOrdinal("qq")),
                                CurrentBatch = (AddGroupQQStatus)reader.GetInt32(reader.GetOrdinal("current_batch")),
                                CurrentBatchAddCount = reader.GetInt32(reader.GetOrdinal("current_batch_add_count")),
                                TotalAdd = reader.GetInt32(reader.GetOrdinal("total_add")),
                                CreateTime = reader.GetDateTime(reader.GetOrdinal("create_time")),
                                UpdateTime = reader.GetDateTime(reader.GetOrdinal("update_time"))
                            };
                        }
                    }
                }
            }

            return addGroupQQ;
        }


        /// <summary>
        /// 获取最新一条记录, 用于从最新更新时间开始添加
        /// </summary>
        /// <returns></returns>
        public async Task<AddGroupQQModel> GetLatestByUpdateTimeAsync()
        {
            AddGroupQQModel addGroupQQ = null;

            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
            SELECT * FROM add_group_qq
            ORDER BY update_time DESC
            LIMIT 1;", connection))
                {
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        if (await reader.ReadAsync())
                        {
                            addGroupQQ = new AddGroupQQModel
                            {
                                Id = reader.GetInt32(reader.GetOrdinal("id")),
                                QQ = reader.GetString(reader.GetOrdinal("qq")),
                                CurrentBatch = (AddGroupQQStatus)reader.GetInt32(reader.GetOrdinal("current_batch")),
                                CurrentBatchAddCount = reader.GetInt32(reader.GetOrdinal("current_batch_add_count")),
                                TotalAdd = reader.GetInt32(reader.GetOrdinal("total_add")),
                                CreateTime = reader.GetDateTime(reader.GetOrdinal("create_time")),
                                UpdateTime = reader.GetDateTime(reader.GetOrdinal("update_time"))
                            };
                        }
                    }
                }
            }

            return addGroupQQ;
        }


        /// <summary>
        /// 获取指定批次的记录数量
        /// </summary>
        /// <param name="currentBatch">指定批次</param>
        /// <returns></returns>
        public async Task<int> GetCurrentBatchCountAsync(int currentBatch)
        {
            int count = 0;

            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
                    SELECT COUNT(*) FROM add_group_qq
                    WHERE current_batch = @CurrentBatch;", connection))
                {
                    command.Parameters.AddWithValue("@CurrentBatch", currentBatch);

                    count = Convert.ToInt32(await command.ExecuteScalarAsync());
                }
            }
            return count;
        }
    }
}